package com.cdshangpai.utils.db;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;

import com.cdshangpai.utils.db.beans.Hotal;

import java.util.ArrayList;
import java.util.List;

public class HotalDb {
    private SQLiteDatabase db=DatabaseHelper.getDb();
    private String tablename="t_hotal";

    public long insert(Hotal hotal)
    {
        long id=db.insert(tablename,null,hotal.getValues());
        return id;
    }

    public void update(Hotal hotal)
    {
        db.update(tablename,hotal.getValues(),"id=?",new String[]{hotal.id.toString()});
    }

    public void delete(Integer id)
    {
        db.delete(tablename,"id=?",new String[]{id.toString()});
    }

    public void clean()
    {
        String sql="delete from "+tablename;
        db.execSQL(sql);
    }

    public Hotal queryById(Integer id)
    {
        Hotal hotal=null;
        String sql="select * from "+tablename+" where id=?";
        ArrayList<String> wheres=new ArrayList<>();
        wheres.add(id.toString());
        Cursor c=db.rawQuery(sql,wheres.toArray(new String[wheres.size()]));
        c.moveToFirst();
        if(!c.isAfterLast())
        {
            hotal=cToHotal(c);
        }
        c.close();
        return hotal;
    }

    public List<Hotal> queryByIds(List<Integer> ids)
    {
        List<Hotal> result=new ArrayList<>();
        StringBuilder sqlBuilder=new StringBuilder();
        ArrayList<String> wheres=new ArrayList<>();
        sqlBuilder.append("select * from ");
        sqlBuilder.append(tablename);
        if(ids.size()>0);
        {
            sqlBuilder.append(" where id in(");
            for(int i=0;i<ids.size();i++)
            {
                sqlBuilder.append("?");
                wheres.add(ids.get(i).toString());
                if(i<ids.size()-1)
                {
                    sqlBuilder.append(",");
                }
            }
            sqlBuilder.append(")");
        }
        Cursor cursor=null;
        String sql=sqlBuilder.toString();
        if(wheres.size()>0)
        {
            String[] params=wheres.toArray(new String[wheres.size()]);
            cursor=db.rawQuery(sql, params);
        }
        else
        {
            cursor=db.rawQuery(sql,null);
        }
        cursor.moveToFirst();
        while (!cursor.isAfterLast())
        {
            Hotal hotal=cToHotal(cursor);
            result.add(hotal);
            cursor.moveToNext();
        }
        return result;
    }

    public ArrayList<Hotal> query(String[] city,String keyword)
    {
        ArrayList<Hotal> result=new ArrayList<>();
        String sql="select * from "+tablename;
        StringBuilder sqlBuilder=new StringBuilder();

        StringBuilder where=new StringBuilder();
        ArrayList<String> wheres=new ArrayList<>();

        if(city!=null&&city.length>0)
        {
            StringBuilder citywhere=new StringBuilder();
            citywhere.append(" city in(");
            for(int i=0;i<city.length;i++)
            {
                citywhere.append("?");
                wheres.add(city[i]);
                if(i!=city.length-1)
                {
                    citywhere.append(",");
                }
            }
            citywhere.append(")");
            sqlBuilder.append(citywhere.toString());
            sqlBuilder.append(" and ");
        }

        if(!TextUtils.isEmpty(keyword))
        {
            String likeKeyword="%"+keyword+"%";
            sqlBuilder.append(" (");
            sqlBuilder.append("name like ? or ");
            wheres.add(likeKeyword);

            sqlBuilder.append("content like ?) and ");
            wheres.add(likeKeyword);
        }

        Cursor c=null;
        if(wheres.size()>0)
        {
            Integer pos=sqlBuilder.lastIndexOf("and");
            sql=sql+" where "+sqlBuilder.substring(0,pos);
            sql+=" order by `index` desc ";
            c=db.rawQuery(sql,wheres.toArray(new String[wheres.size()]));
        }
        else{
            sql+=" order by `index` desc ";
            c=db.rawQuery(sql,null);
        }
        c.moveToFirst();
        while (!c.isAfterLast())
        {
            Hotal hotal=cToHotal(c);
            c.moveToNext();
            result.add(hotal);
        }
        c.close();
        return result;
    }

    private Hotal cToHotal(Cursor c)
    {
        Hotal hotal=new Hotal();
        hotal.id=c.getInt(c.getColumnIndex("id"));
        hotal.index=c.getLong(c.getColumnIndex("index"));
        hotal.name=c.getString(c.getColumnIndex("name"));
        hotal.content=c.getString(c.getColumnIndex("content"));
        hotal.city=c.getString(c.getColumnIndex("city"));
        hotal.titleimg=c.getString(c.getColumnIndex("titleimg"));
        hotal.photos=c.getString(c.getColumnIndex("photos"));
        hotal.version=c.getString(c.getColumnIndex("version"));
        return hotal;
    }

    public void test()
    {
        Hotal hotal=new Hotal();
        hotal.name="name";
        hotal.titleimg="titleimg";
        hotal.content="content";
        hotal.city="city";
        hotal.photos="photos";
        hotal.version="version";

        Long id=insert(hotal);

        hotal=queryById(id.intValue());

        ArrayList<Hotal> hotals=query(new String[] {"city"},null);

        hotal.name="name update";
        update(hotal);

        hotals=query(new String[] {"city"},null);

        delete(id.intValue());

        hotals=query(new String[] {"city"},null);


    }
}
